IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N'[dbo].[sp_Report_ChiTietHangHoaBanRa]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_Report_ChiTietHangHoaBanRa]
GO


CREATE PROC [sp_Report_ChiTietHangHoaBanRa]
(
	@Ma_chi_nhanh		INT,
	@Ngay_xem			DATETIME
)
AS
BEGIN
	SELECT
		D.Ma_hang AS 'Ma_hang', 
		D.Ten AS 'Ten_hang', 
		C.Ten AS 'Ten_khach_hang', 
		SUM(A.So_luong) AS 'So_luong', 
		A.Gia AS 'Gia_ban', 
		SUM(A.Thanh_tien) AS 'Thanh_tien',
		'Ngay_xem' = CONVERT(NVARCHAR, DAY(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, MONTH(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, YEAR(@Ngay_xem))
	FROM ChiTietNhapXuat AS A INNER JOIN PhieuNhapXuat AS B ON A.Ma_phieu_nhap_xuat = B.id
		INNER JOIN KhachHang AS C ON B.Ma_khach_hang = C.id
		INNER JOIN HangHoa AS D ON A.Ma_hang_hoa = D.id
		INNER JOIN ThanhVien AS E ON B.Ma_nhan_vien = E.id
	WHERE E.Ma_chi_nhanh = @Ma_chi_nhanh 
		AND B.Loai_nhap_xuat = 0
		AND DATEDIFF(dd, B.Ngay_cap_nhat_cuoi, @Ngay_xem) = 0
	GROUP BY D.Ma_hang, D.Ten, C.Ten, A.Gia
END
